/*  LocationDBAdapter - Database helper to manage locations 
 
        Copyright (C) 2009 Devendra Laulkar

        This program is free software: you can redistribute it and/or modify
        it under the terms of the GNU General Public License as published by
        the Free Software Foundation version 2 of the License. 

        This program is distributed in the hope that it will be useful,
        but WITHOUT ANY WARRANTY; without even the implied warranty of
        MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
        GNU General Public License for more details.

        You should have received a copy of the GNU General Public License
        along with this program.  If not, see <http://www.gnu.org/licenses/>.
*/
package com.intellBatt;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class LocationDbAdapter {

	public static final String KEY_BSSID = "bssid";
	public static final String KEY_LOCATION_NAME = "name";
	public static final String KEY_ROWID = "_id";
	public static final String KEY_AVG_VALUE = "avg";
	public static final String KEY_MAX_VALUE = "min";
	public static final String KEY_MIN_VALUE = "max";
	public static final String KEY_CONNECTED = "conn";
	public static final String KEY_NUMLOCATED = "num_located";
	public static final String KEY_LASTUSE = "last_use";
	public static final String KEY_CTEXT = "cname";


	public static final int UNSET_AVG_VALUE = 0;

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;

	private static final String DB_CREATE_LOCATION =
		"create table location (_id integer primary key autoincrement, "
		+ "name text not null, num_located integer, last_use text, cname text);";

	private static final String DB_CREATE_MODEL =
		"create table location_model (_id integer, "
		+ "bssid text not null, avg double, max integer, min integer, conn integer);";

	private static final String DATABASE_NAME = "Locations";
	private static final String DATABASE_TABLE_LOCATION = "location";
	private static final String DATABASE_TABLE_MODEL = "location_model";
	private static final int DATABASE_VERSION = 6;

	private final Context ctx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) 
		{
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) 
		{

			db.execSQL(DB_CREATE_LOCATION);
			db.execSQL(DB_CREATE_MODEL);

		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) 
		{
			db.execSQL("DROP TABLE IF EXISTS location");
			db.execSQL("DROP TABLE IF EXISTS location_model");
			onCreate(db);
		}
	}

	public LocationDbAdapter(Context context) 
	{
		this.ctx = context;
	}

	public LocationDbAdapter open() throws SQLException {
		mDbHelper = new DatabaseHelper(ctx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}


	/**
	 * Create a new Location 
	 * 
	 * @return rowId or -1 if failed
	 */
	public long addLocation(String location) 
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_LOCATION_NAME, location);
		initialValues.put(KEY_NUMLOCATED, 1);
		initialValues.put(KEY_LASTUSE,System.currentTimeMillis());
		deleteIfExists(location);
		return mDb.insert(DATABASE_TABLE_LOCATION, null, initialValues);
	}

	public Cursor fetchAllLocations() {

		return mDb.query(DATABASE_TABLE_LOCATION, new String[] {KEY_ROWID, KEY_LOCATION_NAME, KEY_NUMLOCATED, KEY_LASTUSE}, null, null, null, null, null);

	}

	public void deleteIfExists(String location)
	{
		long id = getLocationId(location);
		if(id < 0)
		{
			return;
		}
		else
			deleteLocation(id);
	}

	public long getMaxId()
	{
		Cursor c = mDb.rawQuery("SELECT MAX(_id) FROM " + DATABASE_TABLE_LOCATION,  null);
		if(c == null || c.getCount() == 0)
		{
			return -1;
		}
		c.moveToFirst();
		long ret = c.getLong(0);
		c.close();
		return ret;

	}

	public int getLastNumUse(long rowId)
	{

		Cursor mCursor =
			mDb.query(true, DATABASE_TABLE_LOCATION, new String[] {
					KEY_NUMLOCATED}, KEY_ROWID + "=" + rowId, null,
					null, null, null, null);

		if(mCursor == null || mCursor.getCount() == 0)
		{
			return -1;
		}

		mCursor.moveToFirst();
		int ret = mCursor.getInt(0);
		mCursor.close();
		return ret;

	}
	public boolean incrementAndTouchLocation(long d)
	{
		int increment = getLastNumUse(d);
		if(increment < 0)
		{
			return false;
		}

		increment++;	

		ContentValues args = new ContentValues();
		args.put(KEY_NUMLOCATED, increment);

		args.put(KEY_LASTUSE, "" + System.currentTimeMillis());

		return mDb.update(DATABASE_TABLE_LOCATION, args, KEY_ROWID + "=" + d, null) > 0;

	}

	public boolean updateComment(long id, String comment)
	{
		ContentValues args = new ContentValues();
		args.put(KEY_CTEXT, comment);
		return mDb.update(DATABASE_TABLE_LOCATION, args, KEY_ROWID + "=" + id, null) > 0;
	}
	
	public String getComment(long id)
	{
		Cursor mCursor =
			mDb.query(true, DATABASE_TABLE_LOCATION, new String[] {
					KEY_CTEXT}, KEY_ROWID + "=" + id, null,
					null, null, null, null);
		if(mCursor == null || mCursor.getCount() == 0)
		{
			return null;
		}

		mCursor.moveToFirst();
		String ret = mCursor.getString(0);
		mCursor.close();
		return ret;
	}

	public long getLocationId(String location)
	{
		if(mDb == null)
		{
			Log.e("DBHelper", "mDb is null");
			return -1;
		}
		Cursor mCursor =

			mDb.query(true, DATABASE_TABLE_LOCATION, new String[] {KEY_ROWID,
					KEY_LOCATION_NAME}, KEY_LOCATION_NAME + " like '" + location + "'", null,
					null, null, null, null);


		if(mCursor == null)
			return -1;
		if(mCursor != null && mCursor.getCount() > 0)
		{
			mCursor.moveToFirst();
			long ret = mCursor.getLong(mCursor.getColumnIndex(KEY_ROWID));
			mCursor.close();
			return ret;
		}
		else
		{
			mCursor.close();
			return -1;
		}
	}

	public Cursor fetchLocation(long rowId) throws SQLException {

		Cursor mCursor =
			mDb.query(true, DATABASE_TABLE_LOCATION, new String[] {KEY_ROWID,
					KEY_LOCATION_NAME}, KEY_ROWID + "=" + rowId, null,
					null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}

	/* Delete Location with given rowId */
	public boolean deleteLocation(long rowId) {
		mDb.delete(DATABASE_TABLE_LOCATION, KEY_ROWID + "=" + rowId, null);
		mDb.delete(DATABASE_TABLE_MODEL, KEY_ROWID + "=" + rowId, null);
		return true;
	}

	public boolean deleteLocationModel(long rowId)
	{
		mDb.delete(DATABASE_TABLE_MODEL, KEY_ROWID + "=" + rowId, null);
		return true;
	}

	public Cursor fetchLocationModel(long rowId) throws SQLException {
		Cursor mCursor =

			mDb.query(true, DATABASE_TABLE_MODEL, new String[] {KEY_ROWID,
					KEY_BSSID, KEY_AVG_VALUE, KEY_MAX_VALUE, KEY_MIN_VALUE, KEY_CONNECTED}, KEY_ROWID + "=" + rowId, null,
					null, null, null, null);
		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;
	}

	public long storeSingleModel(long rowId, String bssid, double avg, double max, double min)
	{
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_ROWID, rowId);
		initialValues.put(KEY_BSSID, bssid);
		initialValues.put(KEY_AVG_VALUE, avg);
		initialValues.put(KEY_MAX_VALUE, max);
		initialValues.put(KEY_MIN_VALUE, min);
		initialValues.put(KEY_CONNECTED, 0);
		return mDb.insert(DATABASE_TABLE_MODEL, null, initialValues);
	}
	public long storeSingleModel(long rowId, String bssid, double avg, double max, double min, int conn)
	{

		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_ROWID, rowId);
		initialValues.put(KEY_BSSID, bssid);
		initialValues.put(KEY_AVG_VALUE, avg);
		initialValues.put(KEY_MAX_VALUE, max);
		initialValues.put(KEY_MIN_VALUE, min);
		initialValues.put(KEY_CONNECTED, conn);
		return mDb.insert(DATABASE_TABLE_MODEL, null, initialValues);
	}
}
